<?php
// +----------------------------------------------------------------------
// | NewThink [ Think More,Think Better! ]
// +----------------------------------------------------------------------
// | Copyright (c) 2016~2018 http://www.newthink.cc All rights reserved.
// +----------------------------------------------------------------------
// | 版权所有：山西创泰网络科技有限公司
// +----------------------------------------------------------------------
// | Author: zhangwei  Date:2017/11/15 Time:17:17
// +----------------------------------------------------------------------
namespace phpExcel;

use think\Exception;
use think\Loader;
use think\Db;

class Excel
{
    /**
     * TP5 Excel专用类库
     * $excel=new Excel();
     * $table_name="mk_material_list_edit";
     * $field=["id"=>"序号","guid"=>"项目代码","name"=>"项目名称"];
     * $map=["status"=>1];
     * $map2=["status"=>-1];
     * $excel->setExcelName("下载装修项目")
     * ->createSheet("装修项目",$table_name,$field,$map)
     * ->createSheet("已删除装修项目",$table_name,$field,$map2)
     * ->downloadExcel();
     */
    
    protected $objPHPExcel;
    public $xlsReader;
    public static $instance;
    protected $sheetNum = 0;
    protected $error;
    protected $columnWidth;
    protected $rowHeight = 20;
    protected $excelName;
    protected $isLoad = false;
    //如果你的字段列数超过26字母 会报错
    protected $letterArray = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK"];
    
    protected $cellData = [];
    
    /**
     * $cellData = [
     *
     * ];
     */
    /**
     * 项目初始化
     * Excel constructor.
     */
    public function __construct()
    {
//      Loader::import("com/PHPExcel/PHPExcel");
        $this->objPHPExcel = new \PHPExcel();
        if (!$this->isLoad) {
            //新建时删除默认页面
            $this->objPHPExcel->disconnectWorksheets();
        }
    }
    
    /**
     * @param string $path
     * @return static
     * @throws Exception
     * @throws \PHPExcel_Reader_Exception
     */
    static public function loadExcel($path = "/test.xls")
    {
        if (is_null(self::$instance)) {
            self::$instance = new static();
        }
        $path  = ROOT_PATH . "public/upload/" . $path;
        $excel = self::$instance;
        
        try {
            try {
                $xlsReader = \PHPExcel_IOFactory::createReader("Excel2007");
                $xlsReader->setReadDataOnly(true); //
                $xlsReader->setLoadSheetsOnly(true);
                
                $excel->xlsReader = $xlsReader->load($path);
                //删除导入的文件 //todo 删除失败?
                @unlink($path);
                
            } catch (Exception $e) {
                
                $xlsReader = \PHPExcel_IOFactory::createReader("Excel5");
                $xlsReader->setReadDataOnly(true); //
                $xlsReader->setLoadSheetsOnly(true);
                
                $excel->xlsReader = $xlsReader->load($path);
                
            }
        } catch (Exception $e) {
            throw new Exception("读取EXCEL失败");
        }
        return $excel;
        
    }
    
    public function getSheetByName($name)
    {
        if (isset($this->xlsReader)) {
            return $this->xlsReader->getSheetByName($name);
        } else {
            return false;
        }
        
    }
    
    public function getSheetNames()
    {
        if (isset($this->xlsReader)) {
            return $this->xlsReader->getSheetNames();
        } else {
            return false;
        }
    }
    
    /**
     * @return mixed
     */
    public function getExcelObject()
    {
        return $this->xlsReader;
    }
    
    public function getAllSheets()
    {
        if (isset($this->xlsReader)) {
            return $this->xlsReader->getAllSheets();
        } else {
            return false;
        }
    }
    
    public function getSheetCount()
    {
        if (isset($this->xlsReader)) {
            return $this->xlsReader->getSheetCount();
        } else {
            return false;
        }
    }
    
    public function getSheetArrayByIndex($index = 0)
    {
        if (isset($this->xlsReader)) {
            return $this->xlsReader->getSheet($index)->toArray();
        } else {
            return false;
        }
    }
    
    /**
     * 设置下载的Excel名称
     * @param $name
     * @return $this
     */
    public function setExcelName($name)
    {
        $this->excelName = $name;
        return $this;
    }
    
    /**
     * 返回EXCEL名称
     * @return string
     */
    public function getExcelName()
    {
        return $this->excelName ? $this->excelName : "新建的数据表格";
    }
    
    /**
     * 创建新的Sheet 支持链式操作
     * @param string $sheet_title
     * @param string $table 数据库表名称
     * @param array $field 要导出的字段
     * @param array $map 查询参数
     * @return $this
     * @throws Exception
     * @throws \PHPExcel_Exception
     */
    public function createSheet($sheet_title = "sheet", $list = "", $field = [], $cellStyleValue = '')
    {
        if (empty($list) || empty($field) || !is_array($field)) {
            $this->error = "生成Excel的[list]或[field]参数不正确";
            throw new Exception("生成Excel的[list]或[field]参数不正确");
            return $this;
        }
        
        $sheet_num   = $this->getNewSheetNum();
        $objPHPExcel = $this->objPHPExcel;
        $objPHPExcel->createSheet($sheet_num);
        $objPHPExcel->setActiveSheetIndex($sheet_num);
        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
        
        //设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($this->rowHeight);
        
        //设置垂直居中
        //$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置水平居中
        //$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //获取颜色
        //$color = new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN);
        
        $sheet        = $objPHPExcel->getActiveSheet();
        $field_title  = $field;
        $letter_array = $this->letterArray;
        $outLineCount = 0;
        if (isset($this->cellData)) {
            //设置cell 的值和样式
            foreach ($this->cellData as $key => $val) {
                $sheet->getStyle($val['cell'])->applyFromArray($this->getStyleArray($val['color'], $val['isCenter']));
                $sheet->setCellValue($val['cell'], $val['cellValue']);
                $sheet->mergeCells($val['mergeCells']);
            }
            $outLineCount = count($this->cellData);
        }
        
        foreach ($field_title as $item => $value) {
            if (isset($this->columnWidth)) {
                if (is_array($this->columnWidth) && count($field) == count($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($this->columnWidth[$item]);
                } elseif (is_integer($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($this->columnWidth);
                } else {
                    $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
                }
            } else {
                $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
            }
            
            //标题加粗
            $sheet->getStyle($letter_array[$item] . ($outLineCount + 1))->getFont()->setBold(true);
            $sheet->setCellValue($letter_array[$item] . ($outLineCount + 1), $value);
            //设置标题垂直居中
            $sheet->getStyle($letter_array[$item] . ($outLineCount + 1))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        
        if ($list) {
            foreach ($list as $item => $value) {
                $value = array_values($value);
                foreach ($value as $i => $v) {
                    $sheet->setCellValue($letter_array[$i] . ($item + ($outLineCount + 2)), $value[$i]);
                    //设置垂直居中
                    $sheet->getStyle($letter_array[$i] . ($item + ($outLineCount + 2)))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                }
            }
        }
        return $this;
    }
    
    /**
     * 下载当前的EXCEL
     * @param string $save_name
     * @throws \PHPExcel_Reader_Exception
     */
    public function downloadExcel($save_name = "")
    {
        
        ob_start();
        //最后通过浏览器输出
        $save_name = $this->getExcelName();
        $save_name = $save_name ? "$save_name.xls" : "导出信息.xls";
        header('Content-Type: application/vnd.ms-excel; charset=utf-8');
        header("Content-Disposition: attachment;filename=$save_name");
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        ob_end_flush();//输出全部内容到浏览器
        die();
        
    }
    
    /**
     * 生成并保存excel文件
     * @param string $fileName
     * @param string $path
     * @return string
     */
    public function saveExcel($fileName = '', $path = '')
    {
        ob_end_clean();
        ob_start();
        $fileSaveName = $fileName . date('YmdHis') . '.xls';
        $saveFilePath = './upload/excel_data/' . $fileSaveName;
        $saveFileDir  = dirname($saveFilePath);
        if (!file_exists($saveFileDir)) {
            mkdir($saveFileDir, 0777, true);
        }
        
        $objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5'); //备注:生成'xlsx'格式文件 需要把 Excel5 改为 Excel2007
        $objWriter->save($saveFilePath);
        
        if (!file_exists($saveFilePath)) {
            return false;
        }
        return $saveFilePath;
    }
    
    /**
     * 获取新的Sheet编号
     * @return int
     */
    protected function getNewSheetNum()
    {
        $sheet_num      = $this->sheetNum;
        $this->sheetNum = $sheet_num + 1;
        return $sheet_num;
    }
    
    /**
     * 设置行宽 未设置时候默认为自动
     * @param $width
     * @return $this
     */
    public function setColumnWidth($width)
    {
        if (is_numeric($width) || is_array($width)) {
            $this->columnWidth = $width;
        }
        return $this;
    }
    
    /**
     * 设置默认行高
     * @param $height
     * @return $this
     */
    public function setRowHeight($height)
    {
        if (is_integer($height)) {
            $this->rowHeight = $height;
        }
        return $this;
    }
    
    /**
     * 获取样式数据
     * @param $color
     * @return array
     */
    public function getStyleArray($color, $isHorizontal = false)
    {
        $horizontalClass = \PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
        $horizontal      = $isHorizontal ? $horizontalClass : '';
        
        $styleArray = array(
            'font'      => array(
                'bold'  => true,
                'size'  => 12,
                'color' => array(
                    'argb' => $color ? $color : '00000000',
                ),
            ),
            'alignment' => array(
                'horizontal' => $horizontal,
            ),
        );
        return $styleArray;
    }
    
    /**
     * @author  zhangwei   ${DATE}
     * @param $cellData array [$cell,$color,$value,$mergeCells]    ['要设置的行','颜色','值','要合并的行']
     * @return $this
     */
    public function setCellData($cell, $cellValue, $mergeCells, $color = '', $isCenter = false)
    {
        $this->cellData[] = [
            'cell'       => $cell,
            'cellValue'  => $cellValue,
            'mergeCells' => $mergeCells,
            'color'      => $color,
            'isCenter'   => $isCenter
        ];
        return $this;
    }
    
    /**
     * 魔术方法 有不存在的操作的时候执行
     * @access public
     * @param string $method 方法名
     * @param array $args 参数
     * @return mixed
     */
    public function __call($method, $args)
    {
        call_user_func_array([$this->objPHPExcel, $method], $args);
    }
    
    /**
     * 创建新的Sheet 出差申请单
     * @param string $sheet_title
     * @param $applyInfo
     * @return $this
     * @throws Exception
     * @throws \PHPExcel_Exception
     */
    public function createSheetApply($sheet_title = "sheet", $applyInfo)
    {
        if (empty($applyInfo)) {
            $this->error = "生成Excel的参数不正确";
            throw new Exception("生成Excel的参数不正确");
            return $this;
        }
        //EXCEL的基本设置
        $sheet_num   = $this->getNewSheetNum();
        $objPHPExcel = $this->objPHPExcel;
        $objPHPExcel->createSheet($sheet_num);
        $objPHPExcel->setActiveSheetIndex($sheet_num);
        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
        
        //设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(13.5);
        
        $sheet = $objPHPExcel->getActiveSheet();
        if (isset($this->cellData)) {
            //设置cell 的值和样式
            foreach ($this->cellData as $key => $val) {
                $sheet->getStyle($val['cell'])->applyFromArray($this->getStyleArray($val['color'], $val['isCenter']));
                $sheet->setCellValue($val['cell'], $val['cellValue']);
                $sheet->mergeCells($val['mergeCells']);
            }
        }
        $letter_array = $this->letterArray;
        //设置列宽
        foreach ($this->columnWidth as $item => $value) {
            if (isset($this->columnWidth)) {
                if (is_array($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } elseif (is_integer($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } else {
                    $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
                }
            } else {
                $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
            }
        }
        //填充数据
        //2
        $sheet->getStyle('B2')->getFont()->setBold(true);
        $sheet->getStyle('B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('B2', '出差申请单');
        $sheet->mergeCells('B2:G2');
        //4
        $sheet->getStyle('B4')->getFont()->setBold(true);
        $sheet->setCellValue('B4', '申请单号');
        $sheet->setCellValue('C4', $applyInfo['travel_application_number']);
        $sheet->mergeCells('C4:G4');
        //5
        $sheet->getStyle('B5')->getFont()->setBold(true);
        $sheet->setCellValue('B5', '申请人');
        $sheet->setCellValue('C5', $applyInfo['book_user_name']);
        $sheet->mergeCells('C5:D5');
        $sheet->getStyle('E5')->getFont()->setBold(true);
        $sheet->setCellValue('E5', '申请时间');
        $sheet->setCellValue('F5', explode(' ', $applyInfo['create_time'])[0]);
        $sheet->mergeCells('F5:G5');
        //6
        $sheet->getStyle('B6')->getFont()->setBold(true);
        $sheet->getStyle('B6')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle('B6')->getFill()->getStartColor()->setARGB('FF9c9c9c');
        $sheet->setCellValue('B6', '行程');
        $sheet->mergeCells('B6:G6');
        //7
        $sheet->getStyle('B7')->getFont()->setBold(true);
        $sheet->setCellValue('B7', '出行开始时间');
        $sheet->setCellValue('C7', $applyInfo['travel_time']);
        $sheet->mergeCells('C7:G7');
        //8
        $sheet->getStyle('B8')->getFont()->setBold(true);
        $sheet->setCellValue('B8', '出行截止时间');
        $sheet->setCellValue('C8', $applyInfo['travel_end_time']);
        $sheet->mergeCells('C8:G8');
        //9
        $sheet->getStyle('B9')->getFont()->setBold(true);
        $sheet->setCellValue('B9', '始发地');
        $sheet->setCellValue('C9', $applyInfo['travel_site']);
        $sheet->mergeCells('C9:G9');
        //10
        $sheet->getStyle('B10')->getFont()->setBold(true);
        $sheet->setCellValue('B10', '目的地');
        $sheet->setCellValue('C10', $applyInfo['travel_destination']);
        $sheet->mergeCells('C10:G10');
        //11
        $sheet->getStyle('B11')->getFont()->setBold(true);
        $sheet->setCellValue('B11', '出行人');
        $sheet->setCellValue('C11', $applyInfo['business_name']);
        $sheet->mergeCells('C11:G11');
        //12
        $sheet->getStyle('B12')->getFont()->setBold(true);
        $sheet->setCellValue('B12', '出行原因');
        $sheet->setCellValue('C12', $applyInfo['travel_cause']);
        $sheet->mergeCells('C12:G12');
        //13
        $sheet->getStyle('B13')->getFont()->setBold(true);
        $sheet->setCellValue('B13', '审批人');
        $sheet->setCellValue('C13', $applyInfo['approve_name']);
        $sheet->mergeCells('C13:G13');
        //14
        $sheet->getStyle('B14')->getFont()->setBold(true);
        $sheet->setCellValue('B14', '审批轨迹');
        $sheet->setCellValue('C14', explode(' ', $applyInfo['create_time'])[0] . ' ' . $applyInfo['book_user_name'] . ' 发起申请');
        $sheet->mergeCells('C14:G14');
        $i    = 0;
        $line = 14;
        if (!empty($applyInfo['approved_user'])) {
            foreach ($applyInfo['approved_user'] as $approve) {
                $i++;
                $line++;
                if ($approve['approved_status'] == 0) {
                    $sheet->setCellValue('C' . $line, explode(' ', $approve['approved_time'])[0] . ' ' . $approve['user_name'] . ' 审批通过');
                    if (!empty($approve['sign_url'])) {
                        //用网络地址换取本地存储路径
                        $cloudImg = getCloudImg($approve['sign_url'], './upload/temp/');
                        //这是一个坑,刚开始我把实例化图片类放在了循环外面,但是失败了,也就是每个图片都要实例化一次
                        //图片
                        $objDrawing = new \PHPExcel_Worksheet_Drawing();
                        $objDrawing->setPath($cloudImg);
                        // 设置图片的宽度
                        $objDrawing->setHeight(150);
                        $objDrawing->setWidth(100);
                        //设置图片要插入的单元格
                        $objDrawing->setCoordinates('F' . $line);
                        // 图片偏移距离
                        $objDrawing->setOffsetX(5);
                        $objDrawing->setOffsetY(5);
                        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
                        $start_line = $line;
                        $line       += 3;
                        $i          += 3;
                        $sheet->mergeCells('C' . $start_line . ':E' . $line);
                        $sheet->mergeCells('F' . $start_line . ':G' . $line);
                        $sheet->getStyle('C' . $start_line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    }
                } else {
                    $sheet->setCellValue('C' . $line, explode(' ', $approve['approved_time'])[0] . ' ' . $approve['user_name'] . ' 审批驳回。驳回原因：' . $approve['dismissal_cause']);
                    $sheet->mergeCells('C' . $line . ':G' . $line);
                }
                
            }
        }
        if ($i > 0) {
            $sheet->mergeCells('B14:B' . (14 + $i));
        }
        $sheet->getStyle('B14')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('B5:G' . $line)->applyFromArray($styleArray);
        return $this;
    }
    
    /**
     * 创建新的申请单模板
     * @param string $sheet_title
     * @param $list
     * @return $this
     * @throws Exception
     * @throws \PHPExcel_Exception
     */
    public function createNewApplySheet($sheet_title = "sheet", $applyInfo)
    {
        if (empty($applyInfo)) {
            $this->error = "生成Excel的参数不正确";
            throw new Exception("生成Excel的参数不正确");
            return $this;
        }
        
        //EXCEL的基本设置
        $sheet_num   = $this->getNewSheetNum();
        $objPHPExcel = $this->objPHPExcel;
        $objPHPExcel->createSheet($sheet_num);
        $objPHPExcel->setActiveSheetIndex($sheet_num);
        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
        
        //设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($this->rowHeight);
        
        $sheet = $objPHPExcel->getActiveSheet();
        if (isset($this->cellData)) {
            //设置cell 的值和样式
            foreach ($this->cellData as $key => $val) {
                $sheet->getStyle($val['cell'])->applyFromArray($this->getStyleArray($val['color'], $val['isCenter']));
                $sheet->setCellValue($val['cell'], $val['cellValue']);
                $sheet->mergeCells($val['mergeCells']);
            }
        }
        $letter_array = $this->letterArray;
        //设置列宽
        foreach ($this->columnWidth as $item => $value) {
            if (isset($this->columnWidth)) {
                if (is_array($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } elseif (is_integer($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } else {
                    $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
                }
            } else {
                $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
            }
        }
        
        //设置复杂表头
        $sheet->setCellValue('A1', $applyInfo['company_name'] . chr(10) . '出差申请单');
        $sheet->getStyle('A1')->getFont()->setBold(true);
        $sheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $sheet->mergeCells('A1:D1');
        $sheet->getRowDimension('1')->setRowHeight(32);
        
        $sheet->setCellValue('A2', '部门:' . $applyInfo['department_name']);
        $sheet->mergeCells('A2:B2');
        $sheet->setCellValue('C2', '时间: ' . date('Y年m月d日', strtotime($applyInfo['create_time'])));
        $sheet->mergeCells('C2:D2');
        
        $sheet->setCellValue('A3', '出差事由及办理事项');
        $sheet->mergeCells('A3:A4');
        $sheet->setCellValue('B3', $applyInfo['travel_cause']);
        $sheet->mergeCells('B3:D4');
        
        $sheet->setCellValue('A5', '出差地点');
        $sheet->setCellValue('B5', $applyInfo['travel_destination']);
        $sheet->mergeCells('B5:D5');
        
        $sheet->setCellValue('A6', '出差时间');
        $sheet->setCellValue('B6', '共' . $applyInfo['travel_days'] . '天');
        $sheet->setCellValue('C6', date('Y年m月d日', strtotime($applyInfo['travel_time'])) . '--' . date('Y年m月d日', strtotime($applyInfo['travel_end_time'])));
        $sheet->mergeCells('C6:D6');
        
        $sheet->setCellValue('A7', '借款金额');
        $sheet->setCellValue('B7', $applyInfo['loan_money'] == 0 ? '' : $applyInfo['loan_money']);
        $sheet->mergeCells('B7:D7');
        
        $sheet->setCellValue('A8', '备注');
        $sheet->mergeCells('A8:A9');
        $sheet->setCellValue('B8', $applyInfo['memo']);
        $sheet->getStyle('B8')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('B8:D9');
        
        $sheet->setCellValue('A10', '公司负责人:');
        $sheet->setCellValue('B10', '分管领导:');
        $sheet->setCellValue('C10', '部门:');
        foreach ($applyInfo['approved_user'] as $key => $val) {
            if (!empty($val['sign_url'])) {
                //用网络地址换取本地存储路径
                $cloudImg = getCloudImg($val['sign_url'], './upload/temp/');
                
                $objDrawing = new \PHPExcel_Worksheet_Drawing();
                $objDrawing->setPath($cloudImg);
                // 设置图片的宽度
                $objDrawing->setHeight(160);
                $objDrawing->setWidth(120);
                //设置图片要插入的单元格
                switch ($key) {
                    case 0:
                        $objDrawing->setCoordinates('C10');
                        break;
                    case 1:
                        $objDrawing->setCoordinates('B10');
                        break;
                    case 2:
                        $objDrawing->setCoordinates('A10');
                        break;
                }
                // 图片偏移距离
                $objDrawing->setOffsetX(85);
                $objDrawing->setOffsetY(5);
                $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
            } else {
                $userName = $val['user_name'];
                switch ($key) {
                    case 0:
                        $sheet->setCellValue('C10', '部门：' . $userName);
                        break;
                    case 1:
                        $sheet->setCellValue('B10', '分管领导：' . $userName);
                        break;
                    case 2:
                        $sheet->setCellValue('A10', '公司负责人：' . $userName);
                        break;
                }
            }
        }
        
        $sheet->setCellValue('D10', '申请人：' . $applyInfo['book_user_name']);
        $sheet->getRowDimension('10')->setRowHeight(80);
        $sheet_array = array('A1', 'B2', 'A3', 'B3', 'A5', 'B5', 'A6', 'B6', 'C6', 'A7', 'B7', 'A8', 'A10', 'B10', 'C10', 'D10');
        foreach ($sheet_array as $v) {
            if (in_array($v, ['B3', 'A10', 'B10', 'C10', 'D10'])) {
                $sheet->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            } else {
                $sheet->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }
        }
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:D10')->applyFromArray($styleArray);
        
        return $this;
    }
    
    
    /**
     * 创建新的Sheet 出差报销单
     * @param string $sheet_title
     * @param $expenseInfo
     * @return $this
     * @throws Exception
     * @throws \PHPExcel_Exception
     */
    public function createSheetExpense($sheet_title = "sheet", $expenseInfo, $orderList)
    {
        if (empty($expenseInfo)) {
            $this->error = "生成Excel的参数不正确";
            throw new Exception("生成Excel的参数不正确");
            return $this;
        }
        //EXCEL的基本设置
        $sheet_num   = $this->getNewSheetNum();
        $objPHPExcel = $this->objPHPExcel;
        $objPHPExcel->createSheet($sheet_num);
        $objPHPExcel->setActiveSheetIndex($sheet_num);
        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
        
        //设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(13.5);
        //垂直居中
//        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
        
        $sheet = $objPHPExcel->getActiveSheet();
        if (isset($this->cellData)) {
            //设置cell 的值和样式
            foreach ($this->cellData as $key => $val) {
                $sheet->getStyle($val['cell'])->applyFromArray($this->getStyleArray($val['color'], $val['isCenter']));
                $sheet->setCellValue($val['cell'], $val['cellValue']);
                $sheet->mergeCells($val['mergeCells']);
            }
        }
        $letter_array = $this->letterArray;
        //设置列宽
        foreach ($this->columnWidth as $item => $value) {
            if (isset($this->columnWidth)) {
                if (is_array($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } elseif (is_integer($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } else {
                    $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
                }
            } else {
                $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
            }
        }
        //填充数据
        $line = 1;
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('B' . $line, '出差报销单');
        $sheet->mergeCells('B' . $line . ':I' . $line);
        $line += 2;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '报销单号');
        $sheet->setCellValue('C' . $line, $expenseInfo['expense_code']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '申请人');
        $sheet->setCellValue('C' . $line, $expenseInfo['book_user']['user_name']);
        $sheet->mergeCells('C' . $line . ':D' . $line);
        $sheet->getStyle('E' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $line, '申请时间');
        $sheet->setCellValue('F' . $line, explode(' ', $expenseInfo['create_time'])[0]);
        $sheet->mergeCells('F' . $line . ':I' . $line);
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '出差申请单');
        $sheet->setCellValue('C' . $line, $expenseInfo['request_code']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '出行人');
        $sheet->setCellValue('C' . $line, $expenseInfo['business_travel']['business_traveler'][0]['user_name']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '出行原因');
        $sheet->setCellValue('C' . $line, $expenseInfo['business_travel']['travel_cause']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle('B' . $line)->getFill()->getStartColor()->setARGB('FF9c9c9c');
        $sheet->setCellValue('B' . $line, '行程');
        $sheet->mergeCells('B' . $line . ':I' . $line);
        $line++;
        
        $sheet->setCellValue('B' . $line, '类别');
        $sheet->setCellValue('C' . $line, '开始日期');
        $sheet->setCellValue('D' . $line, '截止日期');
        $sheet->setCellValue('E' . $line, '出发城市');
        $sheet->setCellValue('F' . $line, '目的城市');
        $sheet->setCellValue('G' . $line, '入住城市');
        $sheet->setCellValue('H' . $line, '费用');
        $sheet->setCellValue('I' . $line, '支付方式');
        $sheet->getColumnDimension('H')->setWidth(16); //重置宽度
        
        //订单
        if (!empty($orderList)) {
            foreach ($orderList['plane']['data'] as $item) {
                $line++;
                $sheet->setCellValue('B' . $line, '飞机');
                $sheet->setCellValue('C' . $line, date('Y-m-d', $item['travel_start_time']));
                $sheet->setCellValue('D' . $line, date('Y-m-d', $item['travel_end_time']));
                $sheet->setCellValue('E' . $line, $item['take_off_place']);
                $sheet->setCellValue('F' . $line, $item['to_ground_place']);
                $sheet->setCellValue('H' . $line, $item['order_total_price']);
                $sheet->setCellValue('I' . $line, $item['pay_name']);
            }
            foreach ($orderList['hotel']['data'] as $item) {
                $line++;
                $sheet->setCellValue('B' . $line, '酒店');
                $sheet->setCellValue('C' . $line, date('Y-m-d', $item['travel_start_time']));
                $sheet->setCellValue('D' . $line, date('Y-m-d', $item['travel_end_time']));
                $sheet->setCellValue('G' . $line, $item['city']);
                $sheet->setCellValue('H' . $line, $item['order_total_price']);
                $sheet->setCellValue('I' . $line, $item['pay_name']);
            }
            foreach ($orderList['train']['data'] as $item) {
                $line++;
                $sheet->setCellValue('B' . $line, '火车');
                $sheet->setCellValue('C' . $line, date('Y-m-d', $item['travel_start_time']));
                $sheet->setCellValue('D' . $line, date('Y-m-d', $item['travel_end_time']));
                $sheet->setCellValue('E' . $line, $item['from_station_name']);
                $sheet->setCellValue('F' . $line, $item['to_station_name']);
                $sheet->setCellValue('H' . $line, $item['order_total_price']);
                $sheet->setCellValue('I' . $line, $item['pay_name']);
            }
        }
        if (!empty($expenseInfo['detail'])) {
            $other_num = 0;
            $startLine = $line + 1;
            foreach ($expenseInfo['detail'] as $item) {
                $line++;
                if ($other_num == 0) {
                    $sheet->setCellValue('B' . $line, '其他');
                }
                
                $other_num++;
                $sheet->setCellValue('C' . $line, $item['particulars']);
                $sheet->setCellValue('H' . $line, $item['money'] . '元');
                $sheet->setCellValue('I' . $line, '现付');
                $sheet->mergeCells('C' . $line . ':G' . $line);
            }
            
            if ($other_num > 1) {
                $sheet->mergeCells('B' . $startLine . ':B' . ($startLine + $other_num - 1));
                $sheet->getStyle('B' . $startLine)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }
        }
        
        $line++;
        $sheet->setCellValue('B' . $line, '差旅总金额');
        $sheet->setCellValue('C' . $line, $expenseInfo['order_money'] . '元');
        $sheet->setCellValue('D' . $line, '企业支付金额');
        $sheet->setCellValue('E' . $line, $expenseInfo['company_payment'] . '元');
        $sheet->setCellValue('F' . $line, '借款金额');
        $sheet->setCellValue('G' . $line, $expenseInfo['loan_money'] . '元');
        $sheet->setCellValue('H' . $line, '个人支付总金额');
        $sheet->setCellValue('I' . $line, $expenseInfo['detailed_sum'] . '元');
        
        
        $line++;
        $sheet->setCellValue('B' . $line, '报销金额');
        $sheet->setCellValue('C' . $line, $expenseInfo['total_money'] . '元');
        $sheet->mergeCells('C' . $line . ':I' . $line);
        
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle('B' . $line)->getFill()->getStartColor()->setARGB('FF9c9c9c');
        $sheet->setCellValue('B' . $line, '审批');
        $sheet->mergeCells('B' . $line . ':I' . $line);
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '审批人');
        $sheet->setCellValue('C' . $line, $expenseInfo['approver_name']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $line++;
        $approve_line = $line;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('B' . $line, '审批轨迹');
        $sheet->setCellValue('C' . $line, explode(' ', $expenseInfo['create_time'])[0] . ' ' . $expenseInfo['book_user']['user_name'] . ' 发起申请');
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $i = 0;
        //审批轨迹
        if (!empty($expenseInfo['approver_record'])) {
            foreach ($expenseInfo['approver_record'] as $approve) {
                $i++;
                $line++;
                if ($approve['approved_status'] == 0) {
                    $sheet->setCellValue('C' . $line, explode(' ', $approve['approved_time'])[0] . ' ' . $approve['user_name'] . ' 审批通过');
                    if (!empty($approve['sign_url'])) {
                        //用网络地址换取本地存储路径
                        $cloudImg = getCloudImg($approve['sign_url'], './upload/temp/');
                        //这是一个坑,刚开始我把实例化图片类放在了循环外面,但是失败了,也就是每个图片都要实例化一次
                        //图片
                        $objDrawing = new \PHPExcel_Worksheet_Drawing();
                        $objDrawing->setPath($cloudImg);
                        // 设置图片的宽度
                        $objDrawing->setHeight(150);
                        $objDrawing->setWidth(100);
                        //设置图片要插入的单元格
                        $objDrawing->setCoordinates('H' . $line);
                        // 图片偏移距离
                        $objDrawing->setOffsetX(5);
                        $objDrawing->setOffsetY(5);
                        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
                    }
                    $start_line = $line;
                    $line       += 3;
                    $i          += 3;
                    $sheet->mergeCells('C' . $start_line . ':G' . $line);
                    $sheet->mergeCells('H' . $start_line . ':I' . $line);
                    $sheet->getStyle('C' . $start_line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                } else {
                    $sheet->setCellValue('C' . $line, explode(' ', $approve['approved_time'])[0] . ' ' . $approve['user_name'] . ' 审批驳回。驳回原因：' . $approve['dismissal_cause']);
                    $sheet->mergeCells('C' . $line . ':I' . $line);
                }
                
            }
        }
        //审批轨迹的合并
        if ($i > 0) {
            $sheet->mergeCells('B' . $approve_line . ':B' . ($approve_line + $i));
        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('B4:I' . $line)->applyFromArray($styleArray);
        return $this;
    }
    
    /**
     * 创建新的Sheet 出差补助单
     * @param string $sheet_title
     * @param $allowanceInfo
     * @return $this
     * @throws Exception
     * @throws \PHPExcel_Exception
     */
    public function createSheetAllowance($sheet_title = "sheet", $allowanceInfo, $orderList)
    {
        if (empty($allowanceInfo)) {
            $this->error = "生成Excel的参数不正确";
            throw new Exception("生成Excel的参数不正确");
            return $this;
        }
        //EXCEL的基本设置
        $sheet_num   = $this->getNewSheetNum();
        $objPHPExcel = $this->objPHPExcel;
        $objPHPExcel->createSheet($sheet_num);
        $objPHPExcel->setActiveSheetIndex($sheet_num);
        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
        
        //设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(13.5);
        //垂直居中
//        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
        
        $sheet = $objPHPExcel->getActiveSheet();
        if (isset($this->cellData)) {
            //设置cell 的值和样式
            foreach ($this->cellData as $key => $val) {
                $sheet->getStyle($val['cell'])->applyFromArray($this->getStyleArray($val['color'], $val['isCenter']));
                $sheet->setCellValue($val['cell'], $val['cellValue']);
                $sheet->mergeCells($val['mergeCells']);
            }
        }
        $letter_array = $this->letterArray;
        //设置列宽
        foreach ($this->columnWidth as $item => $value) {
            if (isset($this->columnWidth)) {
                if (is_array($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } elseif (is_integer($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } else {
                    $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
                }
            } else {
                $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
            }
        }
        //填充数据
        $line = 1;
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('B' . $line, '出差补助单');
        $sheet->mergeCells('B' . $line . ':I' . $line);
        $line += 2;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '补助单号');
        $sheet->setCellValue('C' . $line, $allowanceInfo['allowance_code']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '申请人');
        $sheet->setCellValue('C' . $line, $allowanceInfo['book_user']['user_name']);
        $sheet->mergeCells('C' . $line . ':D' . $line);
        $sheet->getStyle('E' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $line, '申请时间');
        $sheet->setCellValue('F' . $line, explode(' ', $allowanceInfo['create_time'])[0]);
        $sheet->mergeCells('F' . $line . ':I' . $line);
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '出差申请单');
        $sheet->setCellValue('C' . $line, $allowanceInfo['request_code']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $line++;
        
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '出行人');
        $sheet->setCellValue('C' . $line, $allowanceInfo['business_users_str']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '出行原因');
        $sheet->setCellValue('C' . $line, $allowanceInfo['business_travel']['travel_cause']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle('B' . $line)->getFill()->getStartColor()->setARGB('FF9c9c9c');
        $sheet->setCellValue('B' . $line, '行程');
        $sheet->mergeCells('B' . $line . ':I' . $line);
        $line++;
        $sheet->setCellValue('B' . $line, '类别');
        $sheet->setCellValue('C' . $line, '开始日期');
        $sheet->setCellValue('D' . $line, '截止日期');
        $sheet->setCellValue('E' . $line, '出发城市');
        $sheet->setCellValue('F' . $line, '目的城市');
        $sheet->setCellValue('G' . $line, '入住城市');
        $sheet->setCellValue('H' . $line, '费用');
        $sheet->setCellValue('I' . $line, '支付方式');
        //订单
        if (!empty($orderList)) {
            foreach ($orderList['plane']['data'] as $item) {
                $line++;
                $sheet->setCellValue('B' . $line, '飞机');
                $sheet->setCellValue('C' . $line, date('Y-m-d', $item['travel_start_time']));
                $sheet->setCellValue('D' . $line, date('Y-m-d', $item['travel_end_time']));
                $sheet->setCellValue('E' . $line, $item['take_off_place']);
                $sheet->setCellValue('F' . $line, $item['to_ground_place']);
                $sheet->setCellValue('H' . $line, $item['order_total_price']);
                $sheet->setCellValue('I' . $line, $item['pay_name']);
            }
            foreach ($orderList['hotel']['data'] as $item) {
                $line++;
                $sheet->setCellValue('B' . $line, '酒店');
                $sheet->setCellValue('C' . $line, date('Y-m-d', $item['travel_start_time']));
                $sheet->setCellValue('D' . $line, date('Y-m-d', $item['travel_end_time']));
                $sheet->setCellValue('G' . $line, $item['city']);
                $sheet->setCellValue('H' . $line, $item['order_total_price']);
                $sheet->setCellValue('I' . $line, $item['pay_name']);
            }
            foreach ($orderList['train']['data'] as $item) {
                $line++;
                $sheet->setCellValue('B' . $line, '火车');
                $sheet->setCellValue('C' . $line, date('Y-m-d', $item['travel_start_time']));
                $sheet->setCellValue('D' . $line, date('Y-m-d', $item['travel_end_time']));
                $sheet->setCellValue('E' . $line, $item['from_station_name']);
                $sheet->setCellValue('F' . $line, $item['to_station_name']);
                $sheet->setCellValue('H' . $line, $item['order_total_price']);
                $sheet->setCellValue('I' . $line, $item['pay_name']);
            }
        }
        $line++;

//        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
//        $sheet->getStyle('B' . $line)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
//        $sheet->getStyle('B' . $line)->getFill()->getStartColor()->setARGB('FF9c9c9c');
//        $sheet->setCellValue('B' . $line, '补助');
//        $sheet->mergeCells('B' . $line . ':I' . $line);
//        $line++;
//        $sheet->setCellValue('B' . $line, '补助金额（元）');
//        $sheet->getStyle('B' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $sheet->mergeCells('B' . $line . ':C' . $line);
//        $sheet->setCellValue('D' . $line, '出行天数');
//        $sheet->getStyle('D' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $sheet->mergeCells('D' . $line . ':E' . $line);
//        $sheet->setCellValue('F' . $line, '酒店补助（元）');
//        $sheet->getStyle('F' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $sheet->mergeCells('F' . $line . ':G' . $line);
//        $sheet->setCellValue('H' . $line, '合计（元）');
//        $sheet->getStyle('H' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $sheet->mergeCells('H' . $line . ':I' . $line);
        
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle('B' . $line)->getFill()->getStartColor()->setARGB('FF9c9c9c');
        $sheet->setCellValue('B' . $line, '补助（标准：' . $allowanceInfo['allowance_standard'] . '元）');
        $sheet->mergeCells('B' . $line . ':I' . $line);
        $line++;
        $sheet->setCellValue('B' . $line, '补助总金额（元）');
        $sheet->getStyle('B' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('B' . $line . ':C' . $line);
        $sheet->setCellValue('D' . $line, '出行天数');
        $sheet->getStyle('D' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('D' . $line . ':E' . $line);
        $sheet->setCellValue('F' . $line, '酒店补助（元）');
        $sheet->getStyle('F' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('F' . $line . ':G' . $line);
        $sheet->setCellValue('H' . $line, '合计（元）');
        $sheet->getStyle('H' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('H' . $line . ':I' . $line);
        
        $line++;
        $sheet->setCellValue('B' . $line, $allowanceInfo['travel_allowance']);
        $sheet->getStyle('B' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('B' . $line . ':C' . $line);
        $sheet->setCellValue('D' . $line, $allowanceInfo['day_of_travel']);
        $sheet->getStyle('D' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('D' . $line . ':E' . $line);
        $sheet->setCellValue('F' . $line, $allowanceInfo['economy_sum']);
        $sheet->getStyle('F' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('F' . $line . ':G' . $line);
        $sheet->setCellValue('H' . $line, $allowanceInfo['total_money']);
        $sheet->getStyle('H' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('H' . $line . ':I' . $line);
        
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle('B' . $line)->getFill()->getStartColor()->setARGB('FF9c9c9c');
        $sheet->setCellValue('B' . $line, '审批');
        $sheet->mergeCells('B' . $line . ':I' . $line);
        $line++;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '审批人');
        $sheet->setCellValue('C' . $line, $allowanceInfo['approve_user_str']);
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $line++;
        $approve_line = $line;
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->getStyle('B' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->setCellValue('B' . $line, '审批轨迹');
        $sheet->setCellValue('C' . $line, explode(' ', $allowanceInfo['create_time'])[0] . ' ' . $allowanceInfo['book_user']['user_name'] . ' 发起申请');
        $sheet->mergeCells('C' . $line . ':I' . $line);
        $i = 0;
        //审批轨迹
        if (!empty($allowanceInfo['approve_record'])) {
            foreach ($allowanceInfo['approve_record'] as $approve) {
                $i++;
                $line++;
                if ($approve['approved_status'] == 0) {
                    $sheet->setCellValue('C' . $line, explode(' ', $approve['approved_time'])[0] . ' ' . $approve['user_name'] . ' 审批通过');
                    if (!empty($approve['sign_url'])) {
                        //用网络地址换取本地存储路径
                        $cloudImg = getCloudImg($approve['sign_url'], './upload/temp/');
                        //这是一个坑,刚开始我把实例化图片类放在了循环外面,但是失败了,也就是每个图片都要实例化一次
                        //图片
                        $objDrawing = new \PHPExcel_Worksheet_Drawing();
                        $objDrawing->setPath($cloudImg);
                        // 设置图片的宽度
                        $objDrawing->setHeight(150);
                        $objDrawing->setWidth(100);
                        //设置图片要插入的单元格
                        $objDrawing->setCoordinates('H' . $line);
                        // 图片偏移距离
                        $objDrawing->setOffsetX(5);
                        $objDrawing->setOffsetY(5);
                        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
                    }
                    $start_line = $line;
                    $line       += 3;
                    $i          += 3;
                    $sheet->mergeCells('C' . $start_line . ':G' . $line);
                    $sheet->mergeCells('H' . $start_line . ':I' . $line);
                    $sheet->getStyle('C' . $start_line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                } else {
                    $sheet->setCellValue('C' . $line, explode(' ', $approve['approved_time'])[0] . ' ' . $approve['user_name'] . ' 审批驳回。驳回原因：' . $approve['dismissal_cause']);
                    $sheet->mergeCells('C' . $line . ':I' . $line);
                }
                
            }
        }
        //审批轨迹的合并
        if ($i > 0) {
            $sheet->mergeCells('B' . $approve_line . ':B' . ($approve_line + $i));
        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('B4:I' . $line)->applyFromArray($styleArray);
        return $this;
    }
    
    /**
     * 创建新的Sheet 出差报销单
     * @param string $sheet_title
     * @param $list
     * @return $this
     * @throws Exception
     * @throws \PHPExcel_Exception
     */
    public function createSheetCountExpense($sheet_title = "sheet", $list)
    {
        if (empty($list)) {
            $this->error = "生成Excel的参数不正确";
            throw new Exception("生成Excel的参数不正确");
            return $this;
        }
        //EXCEL的基本设置
        $sheet_num   = $this->getNewSheetNum();
        $objPHPExcel = $this->objPHPExcel;
        $objPHPExcel->createSheet($sheet_num);
        $objPHPExcel->setActiveSheetIndex($sheet_num);
        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
        
        //设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(13.5);
        //垂直居中
//        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
        
        $sheet = $objPHPExcel->getActiveSheet();
        if (isset($this->cellData)) {
            //设置cell 的值和样式
            foreach ($this->cellData as $key => $val) {
                $sheet->getStyle($val['cell'])->applyFromArray($this->getStyleArray($val['color'], $val['isCenter']));
                $sheet->setCellValue($val['cell'], $val['cellValue']);
                $sheet->mergeCells($val['mergeCells']);
            }
        }
        $letter_array = $this->letterArray;
        //设置列宽
        foreach ($this->columnWidth as $item => $value) {
            if (isset($this->columnWidth)) {
                if (is_array($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } elseif (is_integer($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } else {
                    $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
                }
            } else {
                $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
            }
        }
        //填充数据
        $line = 1;
        $line++;
        $sheet->getStyle('A' . $line)->getFont()->setBold(true);
        $sheet->getStyle('A' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A' . $line, '报销单统计表');
        $sheet->mergeCells('A' . $line . ':K' . $line);
        $line += 2;
        $sheet->getStyle('A' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('A' . $line, '报销单编号');
        $sheet->getStyle('A' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('A' . $line . ':A' . ($line + 1));
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '姓名');
        $sheet->getStyle('B' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('B' . $line . ':B' . ($line + 1));
        $sheet->getStyle('C' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('C' . $line, '行程');
        $sheet->getStyle('C' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('C' . $line . ':C' . ($line + 1));
        $sheet->getStyle('D' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('D' . $line, '申请日期');
        $sheet->getStyle('D' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('D' . $line . ':D' . ($line + 1));
        $sheet->getStyle('E' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $line, '出差申请单编号');
        $sheet->getStyle('E' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('E' . $line . ':E' . ($line + 1));
        $sheet->getStyle('F' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $line, '审核人');
        $sheet->getStyle('F' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('F' . $line . ':F' . ($line + 1));
        $sheet->getStyle('G' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('G' . $line, '差旅金额（元）');
        $sheet->mergeCells('G' . $line . ':J' . $line);
        $sheet->getStyle('K' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('K' . $line, '报销金额(元)');
        $sheet->getStyle('K' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('K' . $line . ':K' . ($line + 1));
        $sheet->getStyle('L' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('L' . $line, '总计报销金额');
        $sheet->getStyle('L' . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->mergeCells('L' . $line . ':L' . ($line + 1));
        
        $line++;
        $sheet->setCellValue('G' . $line, '差旅总金额');
        $sheet->setCellValue('H' . $line, '企业支付总金额');
        $sheet->setCellValue('I' . $line, '借款金额');
        $sheet->setCellValue('J' . $line, '个人支付总金额');
        
        $order_money     = 0;//差旅总金额
        $company_payment = 0;//企业支付总金额
        $loan_money      = 0;//火车总额
        $detailed_sum    = 0;//个人支付总金额
        $total_money     = 0;//报销总金额
        if (!empty($list)) {
            $personal_combined = 0;
            $book_user_id      = '';
            $start_line        = 0;
            foreach ($list as $k => $expense) {
                if ($book_user_id != $expense['book_user_id']) {
                    $start_line        = $line + 1;
                    $personal_combined = 0;
                    $book_user_id      = $expense['book_user_id'];
                    $personal_combined += $expense['total_money'];
                } else {
                    $personal_combined += $expense['total_money'];
                }
                
                $line++;
                $order_money     += $expense['order_money'];
                $company_payment += $expense['company_payment'];
                $loan_money      += $expense['loan_money'];
                $detailed_sum    += $expense['detailed_sum'];
                $total_money     += $expense['total_money'];
                $travel          = '';
                if ($expense['business_travel']['travel_site'] && $expense['business_travel']['travel_destination']) {
                    $travel = $expense['business_travel']['travel_site'] . '->' . $expense['business_travel']['travel_destination'];
                }
                
                $sheet->setCellValue('A' . $line, $expense['expense_code']);
                $sheet->setCellValue('B' . $line, $expense['book_user']['user_name']);
                $sheet->setCellValue('C' . $line, $travel);
                $sheet->setCellValue('D' . $line, explode(' ', $expense['create_time'])[0]);
                $sheet->setCellValue('E' . $line, $expense['request_code']);
                $sheet->setCellValue('F' . $line, $expense['approver_name']);
                $sheet->setCellValue('G' . $line, $expense['order_money']);      //差旅总金额
                $sheet->setCellValue('H' . $line, $expense['company_payment']);  //企业支付总金额
                $sheet->setCellValue('I' . $line, $expense['loan_money']);       //借款金额
                $sheet->setCellValue('J' . $line, $expense['detailed_sum']);     //个人支付金额
                $sheet->setCellValue('K' . $line, $expense['total_money']);      //报销总金额
                $sheet->setCellValue('L' . $start_line, $personal_combined);     //总计报销金额
                
                //合并单元格
                if ($start_line != $line) {
                    $sheet->mergeCells('L' . $start_line . ':L' . $line);
                }
            }
        }
        $line++;
        $sheet->getStyle('A' . $line)->getFont()->setBold(true);
        $sheet->getStyle('A' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A' . $line, '合计');
        $sheet->mergeCells('A' . $line . ':F' . $line);
        $sheet->setCellValue('G' . $line, $order_money);
        $sheet->setCellValue('H' . $line, $company_payment);
        $sheet->setCellValue('I' . $line, $loan_money);
        $sheet->setCellValue('J' . $line, $detailed_sum);
        $sheet->setCellValue('K' . $line, $total_money);
        $sheet->setCellValue('L' . $line, $total_money);
        $styleArray = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A4:L' . $line)->applyFromArray($styleArray);
        return $this;
    }
    
    /**
     * 创建新的Sheet 出差补助单
     * @param string $sheet_title
     * @param $list
     * @return $this
     * @throws Exception
     * @throws \PHPExcel_Exception
     */
    public function createSheetCountAllowance($sheet_title = "sheet", $list)
    {
        if (empty($list)) {
            $this->error = "生成Excel的参数不正确";
            throw new Exception("生成Excel的参数不正确");
            return $this;
        }
        //EXCEL的基本设置
        $sheet_num   = $this->getNewSheetNum();
        $objPHPExcel = $this->objPHPExcel;
        $objPHPExcel->createSheet($sheet_num);
        $objPHPExcel->setActiveSheetIndex($sheet_num);
        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
        
        //设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(13.5);
        
        $sheet = $objPHPExcel->getActiveSheet();
        if (isset($this->cellData)) {
            //设置cell 的值和样式
            foreach ($this->cellData as $key => $val) {
                $sheet->getStyle($val['cell'])->applyFromArray($this->getStyleArray($val['color'], $val['isCenter']));
                $sheet->setCellValue($val['cell'], $val['cellValue']);
                $sheet->mergeCells($val['mergeCells']);
            }
        }
        $letter_array = $this->letterArray;
        //设置列宽
        foreach ($this->columnWidth as $item => $value) {
            if (isset($this->columnWidth)) {
                if (is_array($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } elseif (is_integer($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } else {
                    $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
                }
            } else {
                $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
            }
        }
        //填充数据
        $line = 1;
        $line++;
        $sheet->getStyle('A' . $line)->getFont()->setBold(true);
        $sheet->getStyle('A' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A' . $line, '补助单统计表');
        $sheet->mergeCells('A' . $line . ':I' . $line);
        $line += 2;
        $sheet->getStyle('A' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('A' . $line, '补助单编号');
        $sheet->getStyle('B' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $line, '姓名');
        $sheet->getStyle('C' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('C' . $line, '行程');
        $sheet->getStyle('D' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('D' . $line, '申请日期');
        $sheet->getStyle('E' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('E' . $line, '出差申请单编号');
        $sheet->getStyle('F' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('F' . $line, '审核人');
        $sheet->getStyle('G' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('G' . $line, '差补标准（元/天）');
        $sheet->getStyle('H' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('H' . $line, '出差天数');
        $sheet->getStyle('I' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('I' . $line, '节约归己（元）');
        $sheet->getStyle('J' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('J' . $line, '补助金额（元）');
        $sheet->getStyle('K' . $line)->getFont()->setBold(true);
        $sheet->setCellValue('K' . $line, '补助总金额（元）');
        $total_money = 0;
        $economy_sum = 0;
        if (!empty($list)) {
            $personal_combined = 0;
            $user_id           = '';
            $start_line        = 0;
            
            foreach ($list as $k => $allowance) {
                $travel = '';
                if ($allowance['business_travel']['travel_site'] && $allowance['business_travel']['travel_destination']) {
                    $travel = $allowance['business_travel']['travel_site'] . '->' . $allowance['business_travel']['travel_destination'];
                }
                
                if ($user_id != $allowance['user_id']) {
                    $start_line        = $line + 1;
                    $personal_combined = 0;
                    $user_id           = $allowance['user_id'];
                    $personal_combined += $allowance['total_money'];
                } else {
                    $personal_combined += $allowance['total_money'];
                }
                
                $line++;
                $sheet->setCellValue('A' . $line, $allowance['allowance_code']);
                $sheet->setCellValue('B' . $line, $allowance['book_user']['user_name']);
                $sheet->setCellValue('C' . $line, $travel);
                $sheet->setCellValue('D' . $line, explode(' ', $allowance['create_time'])[0]);
                $sheet->setCellValue('E' . $line, $allowance['request_code']);
                $sheet->setCellValue('F' . $line, $allowance['approve_user_str']);
                $sheet->setCellValue('G' . $line, $allowance['allowance_standard']);
                $sheet->setCellValue('H' . $line, $allowance['day_of_travel']);
                $sheet->setCellValue('I' . $line, $allowance['economy_sum']);
                $sheet->setCellValue('J' . $line, $allowance['total_money']);
                $sheet->setCellValue('K' . $start_line, $personal_combined);
                $economy_sum += $allowance['economy_sum'];
                $total_money += $allowance['total_money'];
                
                //合并单元格
                if ($start_line != $line) {
                    $sheet->mergeCells('K' . $start_line . ':K' . $line);
                }
            }
        }
        $line++;
        $sheet->getStyle('A' . $line)->getFont()->setBold(true);
        $sheet->getStyle('A' . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A' . $line, '合计');
        $sheet->mergeCells('A' . $line . ':H' . $line);
        $sheet->setCellValue('I' . $line, $economy_sum);
        $sheet->setCellValue('J' . $line, $total_money);
        $sheet->setCellValue('K' . $line, $total_money);
        $styleArray = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A4:K' . $line)->applyFromArray($styleArray);
        return $this;
    }
    
    /**
     * 创建营销日报表
     * @param string $sheet_title
     * @param $list
     * @return $this
     * @throws Exception
     * @throws \PHPExcel_Exception
     */
    public function createMarketingDaySheet($sheet_title = "sheet", $list, $field = [], $time = '')
    {
        if (empty($list)) {
            $this->error = "生成Excel的参数不正确";
            throw new Exception("生成Excel的参数不正确");
            return $this;
        }
        
        //EXCEL的基本设置
        $sheet_num   = $this->getNewSheetNum();
        $objPHPExcel = $this->objPHPExcel;
        $objPHPExcel->createSheet($sheet_num);
        $objPHPExcel->setActiveSheetIndex($sheet_num);
        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
        
        //设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($this->rowHeight);
        
        
        $sheet = $objPHPExcel->getActiveSheet();
        if (isset($this->cellData)) {
            //设置cell 的值和样式
            foreach ($this->cellData as $key => $val) {
                $sheet->getStyle($val['cell'])->applyFromArray($this->getStyleArray($val['color'], $val['isCenter']));
                $sheet->setCellValue($val['cell'], $val['cellValue']);
                $sheet->mergeCells($val['mergeCells']);
            }
        }
        $letter_array = $this->letterArray;
        //设置列宽
        foreach ($this->columnWidth as $item => $value) {
            if (isset($this->columnWidth)) {
                if (is_array($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } elseif (is_integer($this->columnWidth)) {
                    $sheet->getColumnDimension($letter_array[$item])->setWidth($value);
                } else {
                    $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
                }
            } else {
                $sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
            }
        }
        
        //设置复杂表头
        $sheet->getStyle('A1')->getFont()->setBold(true);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A1', $sheet_title);
        $sheet->mergeCells('A1:U1');
        
        $sheet->setCellValue('A2', '单位: 营销部');
        $sheet->mergeCells('A2:G2');
        $sheet->setCellValue('H2', '时间: ' . $time);
        $sheet->mergeCells('H2:U2');
        
        //先设置复杂的表头
        $sheet->setCellValue('A3', '单位');
        $sheet->mergeCells('A3:A5');
        $sheet->setCellValue('B3', '类型');
        $sheet->mergeCells('B3:B5');
        $sheet->setCellValue('C3', '签约时间');
        $sheet->mergeCells('C3:C5');
        $sheet->setCellValue('D3', '日流水');
        $sheet->mergeCells('D3:M3');
        $sheet->setCellValue('D4', '机票');
        $sheet->mergeCells('D4:E4');
        $sheet->setCellValue('F4', '火车票');
        $sheet->mergeCells('F4:G4');
        $sheet->setCellValue('H4', '酒店');
        $sheet->mergeCells('H4:I4');
        $sheet->setCellValue('J4', '保险');
        $sheet->mergeCells('J4:K4');
        $sheet->setCellValue('D5', '销售');
        $sheet->setCellValue('E5', '成本');
        $sheet->setCellValue('F5', '销售');
        $sheet->setCellValue('G5', '成本');
        $sheet->setCellValue('H5', '销售');
        $sheet->setCellValue('I5', '成本');
        $sheet->setCellValue('J5', '销售');
        $sheet->setCellValue('K5', '成本');
        $sheet->setCellValue('L4', '退改签');
        $sheet->mergeCells('L4:L5');
        $sheet->setCellValue('M4', '技术服务费');
        $sheet->mergeCells('M4:M5');
        $sheet->setCellValue('N3', '合计');
        $sheet->mergeCells('N3:P3');
        $sheet->setCellValue('N4', '收入（销售+技术服务费）');
        $sheet->mergeCells('N4:N5');
        $sheet->setCellValue('O4', '成本');
        $sheet->mergeCells('O4:O5');
        $sheet->setCellValue('P4', '利润');
        $sheet->mergeCells('P4:P5');
        $sheet->setCellValue('Q3', '累计');
        $sheet->mergeCells('Q3:S3');
        $sheet->setCellValue('Q4', '收入');
        $sheet->mergeCells('Q4:Q5');
        $sheet->setCellValue('R4', '成本');
        $sheet->mergeCells('R4:R5');
        $sheet->setCellValue('S4', '利润');
        $sheet->mergeCells('S4:S5');
        $sheet->setCellValue('T3', '回款(财务部)');
        $sheet->mergeCells('T3:T5');
        $sheet->setCellValue('U3', '备注');
        $sheet->mergeCells('U3:U5');
        
        $sheet_array = array('A1', 'A3', 'B3', 'C3', 'D3', 'D4', 'F4', 'H4', 'J4', 'D5', 'E5', 'H5', 'I5', 'J5', 'K5', 'L4', 'M4', 'N3', 'N4', 'O4', 'P4', 'Q3', 'Q4', 'R4', 'S4', 'T3', 'U3');
        foreach ($sheet_array as $v) {
            $sheet->getStyle($v)->getFont()->setBold(true);
            $sheet->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        }
        
        $line = 5;
        foreach ($list as $key => $val) {
            $line += 1;
            for ($i = 0; $i < count($val); $i++) {
                $sheet->setCellValue($this->letterArray[$i] . $line, $val[$field[$i]]);
                $sheet->getStyle($this->letterArray[$i] . $line)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle($this->letterArray[$i] . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }

//            $start = $line  -2;
//            $end   =  $line -1;
//            if ($key % 2 == 0) {
//                $sheet->mergeCells('A'.$start.':A'.$end);
//            }
        }
        
        $line += 1;
        $sheet->setCellValue('A' . $line, '财务审核人: ');
        $sheet->mergeCells('A' . $line . ':G' . $line);
        $sheet->setCellValue('H' . $line, '部门经理: ');
        $sheet->mergeCells('H' . $line . ':S' . $line);
        
        $styleArray = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),
            
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:U' . $line)->applyFromArray($styleArray);
        
        return $this;
    }
}